Benton Cheng

Data Science for Public Health HW4 - Question 4

Write number of pills in terms of millions in seperate column:

import pandas as pd
import plotly.express as px
import sqlite3 as sq3

# Reading .db file and creating a few pandas dataframes from it

con = sq3.connect('opioid.db') # has annual, land, and population tables

annual = pd.read_sql_query('SELECT * from annual', con)
land = pd.read_sql_query('SELECT * from land', con)
pop = pd.read_sql_query('SELECT * from population', con)

con.close

<function Connection.close>

annual = annual.assign(Pills_in_millions = pd.to_numeric(annual.DOSAGE_UNIT)/1000000)

annual

	?	BUYER_COUNTY	BUYER_STATE	year	count	DOSAGE_UNIT	countyfips	Pills_in_millions
0	1	ABBEVILLE	SC	2006	877	363620	45001	0.36362
1	2	ABBEVILLE	SC	2007	908	402940	45001	0.40294
2	3	ABBEVILLE	SC	2008	871	424590	45001	0.42459
3	4	ABBEVILLE	SC	2009	930	467230	45001	0.46723
4	5	ABBEVILLE	SC	2010	1197	539280	45001	0.53928
...	...	...	...	...	...	...	...	...
27753	27754	NA	NV	2007	447	200600	NA	0.20060
27754	27755	NA	NV	2008	5	2200	NA	0.00220
27755	27756	NA	OH	2006	23	5100	NA	0.00510
27756	27757	NA	PR	2006	10	17800	NA	0.01780
27757	27758	NA	PR	2007	2	1300	NA	0.00130

27758 rows × 8 columns

# Find avg pills in millions per year
avg_df = annual.groupby('year').mean()
avg_df.reset_index(inplace = True)

avg_df

	year	Pills_in_millions
0	2006	2.645410
1	2007	2.992099
2	2008	3.252162
3	2009	3.526038
4	2010	3.783656
5	2011	4.035583
6	2012	3.993064
7	2013	3.861752
8	2014	3.768738

fig = px.line(avg_df, x = 'year', y = 'Pills_in_millions', markers = True)

fig.update_layout(
    title="Average Annual Opioid Pills Shipped Per County",
    xaxis_title="Year",
    yaxis_title="Annual Pills Shipped Per County (millions)",
    legend_title="Legend Title"
)



fig.show()